﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Collections;
using DelonixRegiaSystem.Classes;
using System.Configuration;
using DelonixRegiaSystem;

namespace DelonixRegiaSystem.Models
{
    public class ReportingDBManager
    {
        public static ArrayList GetRoomReportStatus(string Status)
        {
            ArrayList Room = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelorixConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from room WHERE Status=@Status";
                comm.Parameters.AddWithValue("@Status", Status);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    room r = new room();
                    r.roomNum = (int)dr["roomNum"];
                    r.roomType = (string)dr["roomType"];
                    r.roomFloor = (string)dr["roomFloor"];
                    r.Status = (string)dr["Status"];
                    Room.Add(r);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return Room;
        }

        public static guest GetGuestInRoom(int roomNum)
        {
            guest g = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelorixConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from guest WHERE roomNum=@roomNum";
                comm.Parameters.AddWithValue("@roomNum", roomNum);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    g = new guest();
                    g.roomNum = (int)dr["roomNum"];
                    g.firstName = (string)dr["firstName"];
                    g.lastName = (string)dr["lastName"];
                    g.country = (string)dr["country"];
                    g.phoneNo = (string)dr["phoneNo"];
                    g.checkIn = (DateTime)dr["checkIn"];
                    g.checkOut = (DateTime)dr["checkOut"];


                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return g;
        }

        public static ArrayList GetAllGuestSpecificRoom(int roomNum)
        {
            ArrayList Guest = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelorixConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from guest WHERE roomNum=@roomNum";
                comm.Parameters.AddWithValue("@roomNum", roomNum);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    guest g = new guest();
                    g.roomNum = (int)dr["roomNum"];
                    g.firstName = (string)dr["firstName"];
                    g.lastName = (string)dr["lastName"];
                    g.country = (string)dr["country"];
                    g.phoneNo = (string)dr["phoneNo"];
                    g.checkIn = (DateTime)dr["checkIn"];
                    g.checkOut = (DateTime)dr["checkOut"];
                    Guest.Add(g);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return Guest;
        }
        public static ArrayList GetAllGuestAllRoom()
        {
            ArrayList Guest = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelorixConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from guest";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    guest g = new guest();
                    g.roomNum = (int)dr["roomNum"];
                    g.firstName = (string)dr["firstName"];
                    g.lastName = (string)dr["lastName"];
                    g.country = (string)dr["country"];
                    g.phoneNo = (string)dr["phoneNo"];

                    Guest.Add(g);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return Guest;
        }
        public static ArrayList RoomOccupancyReportForASpecificDate(DateTime checkIn)
        {
            ArrayList booking = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelorixConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from booking WHERE checkIn = @checkIn";
                comm.Parameters.AddWithValue("@checkIn", checkIn);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    booking b = new booking();
                    b.roomNum = (int)dr["roomNum"];
                    b.firstName = (string)dr["firstName"];
                    b.lastName = (string)dr["lastName"];
                    b.guestNo = (int)dr["guestNo"];
                    b.checkIn = (DateTime)dr["checkIn"];
                    b.checkOut = (DateTime)dr["checkOut"];
                    booking.Add(b);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return booking;
        }
        public static ArrayList RoomOccupancyReportForARangeofDates(DateTime checkIn, DateTime checkOut)
        {
            ArrayList booking = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelorixConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from booking WHERE checkIn >= @checkIn and checkOut <= @checkOut";
                comm.Parameters.AddWithValue("@checkIn", checkIn);
                comm.Parameters.AddWithValue("@checkOut", checkOut);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    booking b = new booking();
                    b.roomNum = (int)dr["roomNum"];
                    b.firstName = (string)dr["firstName"];
                    b.lastName = (string)dr["lastName"];
                    b.guestNo = (int)dr["guestNo"];
                    b.checkIn = (DateTime)dr["checkIn"];
                    b.checkOut = (DateTime)dr["checkOut"];
                    booking.Add(b);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return booking;
        }
    }

}